/*
* Copyright (C) 2012 Manuel Pérez
*
* This file is part of OpenSudoku.
*
* OpenSudoku is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* OpenSudoku is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with OpenSudoku. If not, see <http://www.gnu.org/licenses/>.
*
*/
package dBasea;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import modeloa.Koloreak;
import modeloa.Modeloa;

public class Kotxea {

	public boolean existitzenDaMatrikula(String mat) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "select matrikula FROM ibilgailuaseguratuak WHERE matrikula ='"
				+ mat + "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		rs.close();
		return aurki;

	}

	public Vector<Koloreak> getKoloreak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM Kolorea";
		ResultSet rs = dbk.executeSQL(query);

		Vector<Koloreak> data = new Vector<Koloreak>();
		if (rs.next()) {
			do {
				Koloreak Lista = new Koloreak(rs.getInt(1), rs.getString(2));

				data.addElement(Lista);

			} while (rs.next());
			return data;
		} else {
			return null;
		}
	}

	public Vector<Modeloa> getModeloa(String marka) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM Modeloa m,Marka a where a.markaIzena='"
				+ marka + "' and m.idMarka=a.idMarka";
		ResultSet rs = dbk.executeSQL(query);

		Vector<Modeloa> data = new Vector<Modeloa>();
		if (rs.next()) {
			do {
				Modeloa Lista = new Modeloa(rs.getInt(1), rs.getString(2),
						rs.getInt(3));

				data.addElement(Lista);

			} while (rs.next());
			return data;
		} else {
			return null;
		}
	}

	public List<String> getMarkak() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT markaIzena FROM Marka order by markaIzena";
		ResultSet rs = dbk.executeSQL(query);
		List<String> x = new ArrayList<String>();
		while (rs.next()) {
			x.add(rs.getString(1));
		}
		rs.close();

		return x;
	}

	public void kotxeSortu(String matrikula, String kolorea, String modeloa)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO IbilgailuAseguratuak (matrikula, idKolorea, idModeloa)"
				+ " VALUES ('"
				+ matrikula
				+ "', (select idKolorea from Kolorea where koloreIzena = '"
				+ kolorea
				+ "'), (select idModeloa from Modeloa where ModeloIzena = '"
				+ modeloa + "'))";

		dbk.executeSQL(query);

	}

	public boolean existitzenDaUrtea(java.util.Date data) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT urtea FROM BezeroIbilgailuak WHERE urtea='"
				+ data + "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		return aurki;
	}

	public void kotxeBezeroaSortu(String matrikula, String bezeroId, long dataf)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO BezeroIbilgailuak (matrikula, bezeroId, aseguruTipoKodigoa, urtea)"
				+ " VALUES ('"
				+ matrikula
				+ "', '"
				+ bezeroId
				+ "', (select aseguruTipoKodigoa from BezeroAseguratua where bezeroID = '"
				+ bezeroId + "')," + dataf + ")";

		dbk.executeSQL(query);

	}

	public boolean existitzenDaModeloa(String mod) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM Modeloa WHERE modeloIzena='" + mod + "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		return aurki;
	}

	public boolean existitzenDaMarka(String marka) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM Marka WHERE markaIzena='" + marka + "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		return aurki;
	}

	public void modeloaSortu(String izena, String marka) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO Modeloa (modeloIzena, idMarka)"
				+ " VALUES ('" + izena
				+ "', (select idMarka from Marka where markaIzena = '" + marka
				+ "'))";

		dbk.executeSQL(query);

	}

	public void markaSortu(String marka) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO Marka (markaIzena)" + " VALUES ('" + marka
				+ "' )";

		dbk.executeSQL(query);

	}
}